package com.example.clock.controlClass;

import static java.security.AccessController.getContext;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.icu.util.Calendar;
import android.widget.Toast;

import com.example.clock.basicClass.Plan;
import com.example.clock.basicClass.Tags;
import com.example.clock.basicClass.Task;

import java.lang.reflect.Array;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Date;

public class SqlController {//连接并使用sqlite数据库撒旦士大夫

    //创建、更新数据库

    //获取所有标签
    public static ArrayList<Tags> getAllTags(SQLiteDatabase db){
        ArrayList<Tags> arrayList = new ArrayList<Tags>();
        Cursor c = db.query("Tags",null,null,null,null,null,"bigTag,smallTag",null);
        ArrayList<String> smallTags;
        String currentBigTag = null;
        String currentSmallTag = null;
        if(c.moveToFirst() == true){//不为空
             do{
                //获取数据
                @SuppressLint("Range") String bigTag = c.getString(c.getColumnIndex("bigTag"));
                @SuppressLint("Range") String smallTag = c.getString(c.getColumnIndex("smallTag"));

                // 第一次循环
                if (currentBigTag == null) {
                    smallTags = new ArrayList<String>();
                    smallTags.add(smallTag);
                    arrayList.add(new Tags(bigTag,smallTags));
                    currentBigTag = bigTag;
                    currentSmallTag = smallTag;
                }
                //和上一个大标签一样
                else if (currentBigTag.equals(bigTag) == true) {
                    if(currentSmallTag.equals(smallTag) == true) {//和上一个小标签一样
                        }
                    else {
                        arrayList.get(arrayList.size() - 1).getSmallTags().add(smallTag);
                        currentSmallTag = smallTag;
                    }
                }
                //和上一个大标签不一样
                else if(currentBigTag.equals(bigTag) == false){
                        smallTags = new ArrayList<String>();
                        smallTags.add(smallTag);
                        arrayList.add(new Tags(bigTag,smallTags));
                        currentBigTag = bigTag;
                        currentSmallTag = smallTag;
                }
            }while (c.moveToNext() == true);
        }


        return arrayList;
    }
    //新增大标签函数
    @SuppressLint("Range")
    public static void addBigTag(SQLiteDatabase db, String BigTag) {
        int x = 0;//判断是否有重复 0是没有，1是有
        Cursor c = db.query("BigTags",null,null,null,null,null,null);
        String currentBigTag = null;
        if(c.moveToFirst() == false){//没有元素，就直接添加
            ContentValues cv = new ContentValues();
            cv.put("bigTag", BigTag);
            db.insert("BigTags", null, cv);
        }
        else{
            //判断
            currentBigTag = c.getString(c.getColumnIndex("bigTag"));
            do {
                if(currentBigTag.equals(BigTag)){
                    x = 1;
                    break;
                }
            }while(c.moveToNext());


            //添加or不添加  x = 0就添加
            if(x == 0){
                ContentValues cv = new ContentValues();
                cv.put("bigTag", BigTag);
                db.insert("BigTags", null, cv);
            }
        }

    };



    //新增小标签函数
    public static void addSmallTag(SQLiteDatabase db,String BigTag,String SmallTag){
        ContentValues cv = new ContentValues();
        cv.put("bigTag",BigTag);
        cv.put("smallTag",SmallTag);
        db.insert("Tags",null,cv);
    }

    //修改大标签
    public static  void changeBigTag(SQLiteDatabase db,String oldBigTag,String newBigTag){
        ContentValues values = new ContentValues();
        values.put("bigTag", newBigTag);
        // 更新第一个表
        int rowsAffectedFirstTable = db.update("bigtags", values, "bigTag = ?", new String[]{oldBigTag});

        // 更新第二个表
        int rowsAffectedSecondTable = db.update("tags", values, "bigTag = ?", new String[]{oldBigTag});
    }

    //删除大标签
    public static void deleteBigTag(SQLiteDatabase db,String BigTag){
        String whereClause = "bigTag = ?";
        String[] whereArgs = {BigTag};
        db.delete("tags", whereClause, whereArgs);
        db.delete("bigtags", "bigTag = ?", whereArgs);
    }
    //删除小标签
    public static void deleteSmallTag(SQLiteDatabase db,String BigTag,String SmallTag){
        String whereClause = "bigTag = ? AND smallTag = ?";
        String[] whereArgs = {BigTag, SmallTag};
        db.delete("tags", whereClause, whereArgs);
    }



    //TODO 新增task
    public static void addTask(SQLiteDatabase db,Task task){
        //编号
        Integer count = getTaskCount(db);
        count ++;
        task.setTaskId(count.toString());
        //插入
        ContentValues cv = task.toCV();
        db.insert("Tasks",null,cv);
    };
    //todo 查找某天task
    public static ArrayList<Task> getTaskByDate(SQLiteDatabase db,String date){
        ArrayList<Task> arrayList = new ArrayList<>();
        Cursor c = db.query("Tasks",null,"date = '"+date+"'",null,null,null,"startTime");
        while(c.moveToNext()){
            arrayList.add(SqlController.cursorToTask(c));
        }


        return arrayList;
    };
    //todo 查找某几天task
    public static ArrayList<ArrayList<Task>> RangeSearchTaskByDate(SQLiteDatabase db,String startDate,String endDate){
        ArrayList<Task> arrayList = new ArrayList<>();
        ArrayList<ArrayList<Task>> arr = new ArrayList<>();
        String[] result = startDate.split("-");
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");

        // 将字符串转换为 LocalDate
        LocalDate date1 = LocalDate.parse(startDate, formatter);
        LocalDate date2 = LocalDate.parse(endDate, formatter);
        Integer year = date1.getYear();
        Integer month = date1.getMonthValue();
        Integer day = date1.getDayOfMonth();
        while(date1.equals(date2) == false){
            arrayList = SqlController.getTaskByDate(db,year.toString()+"-"+ month.toString()+"-"+day.toString());
            arr.add(arrayList);
        date1 = date1.plusDays(1);
        };
        arr.add(SqlController.getTaskByDate(db,endDate));

        return arr;
    }
    //todo 获取所有与id绑定的Task
    public static ArrayList<Task> getTaskByPlan(SQLiteDatabase db, String planId) {
        ArrayList<Task> arrayList = new ArrayList<>();
        Cursor c = db.query("Tasks",null,"planId = "+planId,null,null,null,"startTime");
        while(c.moveToNext()){
            arrayList.add(SqlController.cursorToTask(c));
        }
        return arrayList;
    }
    public static Task getTaskById(SQLiteDatabase db, String taskId) {
        ArrayList<Task> arrayList = new ArrayList<>();
        Cursor c = db.query("Tasks",null,"taskId = "+taskId,null,null,null,"startTime");
        while(c.moveToNext()){
            arrayList.add(SqlController.cursorToTask(c));
        }
        return arrayList.get(0);
    }
    public static Task getLatestTask(SQLiteDatabase db) {
        Integer taskId = getTaskCount(db);
        ArrayList<Task> arrayList = new ArrayList<>();
        Cursor c = db.query("Tasks",null,"taskId = "+taskId,null,null,null,"startTime");
        while(c.moveToNext()){
            arrayList.add(SqlController.cursorToTask(c));
        }
        return arrayList.get(0);
    }
    //todo 增加plan次数和总时长
    public static void changePlanByTimeAndTimes(SQLiteDatabase db,String planId,boolean finished,int lastTime){
        Cursor c = db.query("plans",null,"planId = "+planId,null,null,null,null);
        Plan plan = null;
        if(c.moveToNext()) {
            plan = cursorToPlan(c);//获取plan

            //设置新的完成次数
            int finish = plan.getFinishTimes();
            if(finished)finish++;

            //设置新的时间
            int lasttime = plan.getLastTime();
            lasttime += lastTime;

            //修改更新
            ContentValues values = new ContentValues();
            values.put("finishtimes", finish);
            values.put("lasttime", lasttime);
            db.update("plans", values, "planid=?", new String[]{planId});
        }
    };

    //todo 新增plan
    @SuppressLint("Range")
    public static void addPlan(SQLiteDatabase db, Plan plan){
        //获取所有Id
        String[] planIdArr = {"planId"};
        Cursor c = db.query("plans",planIdArr,null,null,null,null,null);
        Integer Max = 0;
        while(c.moveToNext()){
            Integer now = Integer.parseInt(c.getString(c.getColumnIndex("planId")));
            if(now >= Max)Max = now;
        }
        //编号
        Max++;
        plan.setPlanId(Max.toString());
        //插入
        ContentValues cv = plan.toCV();
        db.insert("Plans",null,cv);
    }

    //todo 查找所有plan
    public static ArrayList<Plan> getAllPlans(SQLiteDatabase db){
        ArrayList<Plan> arrayList = new ArrayList<Plan>();
        Cursor c = db.query("Plans",null,null,null,null,null,"endDate");
        while(c.moveToNext()){
            arrayList.add(cursorToPlan(c));
        }
        return arrayList;
    }

    //todo 查找今日满足的plan
    @SuppressLint("Range")
    public static ArrayList<Plan> getTodayPlans(SQLiteDatabase db){


        ArrayList<Plan> arrayList = new ArrayList<Plan>();
        Cursor c = db.query("Plans",null,null,null,null,null,"endDate");
        LocalDate today = LocalDate.now();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        String todayStr =  today.format(formatter);
        while(c.moveToNext()){
            if(compareDate(todayStr,c.getString(c.getColumnIndex("startDate"))) >= 0){//先判断是否today>startDate
               if(compareDate(todayStr,c.getString(c.getColumnIndex("endDate"))) <= 0) {//在判断是否today<endDate
                   arrayList.add(cursorToPlan(c));
               }
            }
        }
        return arrayList;
    }


    //todo 根据id获取Plan
    public static Plan findPlanById(SQLiteDatabase db,String planId){
        Cursor c = db.query("Plans",null,"planId ="+planId,null,null,null,null);
        c.moveToFirst();
        Plan plan = cursorToPlan(c);
        return plan;
    }


    //todo 删除plan
    public static void deletePlan(SQLiteDatabase db, String planId){
        db.execSQL("DELETE FROM plans WHERE planId =" + planId);
    }
    //todo 修改plan
    public static void changePlan(SQLiteDatabase db,Plan newPlan){
        String planId = newPlan.getPlanId();
        deletePlan(db,planId);//删除旧的
        db.insert("Plans",null,newPlan.toCV());
    }
    //todo 使用强制解锁后的次数-1//返回true则可以解锁，而且已经解锁成功，返回false则不能解锁
    @SuppressLint("Range")
    public static Boolean ForceUnlockTime(SQLiteDatabase db){
        Cursor c = db.query("unlockedSetting",null,null,null,null,null,null);
        c.moveToFirst();
        @SuppressLint("Range") String lastDate = c.getString(c.getColumnIndex("id"));
        String today = getTodayString();
        String[] str1 = lastDate.split("-");
        String[] str2  =  today.split("-");
        if(Integer.parseInt(str2[0]) > Integer.parseInt(str1[0])){//今天年份大
            ContentValues cv = new ContentValues();
            cv.put("id",today);
            cv.put("forceUnclockTime",2);
            db.update("unlockedSetting",cv,"id = "+lastDate,null);
            return true;
        }
        if (Integer.parseInt(str2[0]) == Integer.parseInt(str1[0])){//年份相等
            if(Integer.parseInt(str2[1]) > Integer.parseInt(str1[1])){//月份大
                ContentValues cv = new ContentValues();
                cv.put("id",today);
                cv.put("forceUnclockTime",2);
                db.update("unlockedSetting",cv,"id = "+lastDate,null);
                return true;
            }
            if(Integer.parseInt(str2[1]) > Integer.parseInt(str1[1])){//相等
                int times = Integer.parseInt(c.getString(c.getColumnIndex("forceUnclockTime")));
                if(times > 0){
                    times --;
                    ContentValues cv = new ContentValues();
                    cv.put("id",today);
                    cv.put("forceUnclockTime",times);
                    db.update("unlockedSetting",cv,"id = "+lastDate,null);
                    return true;
                }

            }
        }
        return false;
    }





    //辅助函数
    //获取task的个数
    public static int getTaskCount(SQLiteDatabase db){//获取task的个数
        Cursor c = db.query("Tasks",null,null,null,null,null,null);
        return c.getCount();
    }
    public static int getPlanCount(SQLiteDatabase db){//获取task的个数
        Cursor c = db.query("Plans",null,null,null,null,null,null);
        return c.getCount();
    }
    //将cursor类转化为task类
    public static Task cursorToTask(Cursor c){
        @SuppressLint("Range") Task t = new Task(c.getString(c.getColumnIndex("taskId")),c.getString(c.getColumnIndex("taskName")),c.getString(c.getColumnIndex("tips")),c.getString(c.getColumnIndex("date")),c.getString(c.getColumnIndex("startTime")),c.getString(c.getColumnIndex("endTime")),Integer.parseInt(c.getString(c.getColumnIndex("lastTime"))),Integer.parseInt(c.getString(c.getColumnIndex("planTime"))),c.getString(c.getColumnIndex("largeTags")),c.getString(c.getColumnIndex("smallTags")),c.getString(c.getColumnIndex("planId")),Integer.parseInt(c.getString(c.getColumnIndex("clockedTask"))),Integer.parseInt(c.getString(c.getColumnIndex("clockedMode"))),Integer.parseInt(c.getString(c.getColumnIndex("unlocked"))));
        return t;


    }
    public static Plan cursorToPlan(Cursor c){
        @SuppressLint("Range") Plan p = new Plan(c.getString(c.getColumnIndex("planId")),c.getString(c.getColumnIndex("planName")),c.getString(c.getColumnIndex("startDate")),c.getString(c.getColumnIndex("endDate")),Integer.parseInt(c.getString(c.getColumnIndex("preTimes"))),Integer.parseInt(c.getString(c.getColumnIndex("finishTimes"))),Integer.parseInt(c.getString(c.getColumnIndex("lastTimeByTimes"))),Integer.parseInt(c.getString(c.getColumnIndex("lastTime"))),c.getString(c.getColumnIndex("largeTags")),c.getString(c.getColumnIndex("smallTags")));
        return p;
    }
    public static Date addDay(int n,Date d){
        int addDay = n;//指定增加天数
        Calendar c = Calendar.getInstance();
        c.setTime(d);
        c.add(Calendar.DAY_OF_MONTH, addDay);
        return c.getTime();
    }
    //比较日期  date1-date2
    public static int compareDate(String date1,String date2){
        String[] s1 = date1.split("-");
        String[] s2 = date2.split("-");
        if(Integer.parseInt(s1[0]) - Integer.parseInt(s2[0]) == 0){
            if(Integer.parseInt(s1[1]) - Integer.parseInt(s2[1]) == 0){
                return Integer.parseInt(s1[2]) - Integer.parseInt(s2[2]);
            }
            else {
                return Integer.parseInt(s1[1]) - Integer.parseInt(s2[1]);
            }
        }
        else {
            return Integer.parseInt(s1[0]) - Integer.parseInt(s2[0]);
        }
    }
    //获取今天的日期"xx-xx-xx"
    public static String getTodayString(){
        LocalDate today = LocalDate.now();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        String todayStr =  today.format(formatter);
        return todayStr;
    };


}
